<?php

/**
 * Student activities on the last 12 months
 * @package ModuleStatistiques
 * @version 1.0b
 * @return JSON
 */

session_start();

// Configuration file
include '../../configuration.php';
// Functions
include '../../inc/functions.php';

// Checking if user is logged in
if (!CheckSession()):
	exit();
endif;

// Database connexion string
$db_str = "mysql:dbname=".$configuration['db'].";host=".$configuration['host'];

// Collecting numeric stats
try {
	
	// Connecting to database
	$db = new PDO($db_str, $configuration['user'], $configuration['pass']);
	
	// Query
	$sql  = 'SELECT company.name AS "COMPANY", DATE_FORMAT(gotstudentactivity.from, "%b-%y") AS "FROM", DATE_FORMAT(gotstudentactivity.to, "%b-%y") AS "TO" ';
	$sql .= 'FROM gotstudentactivity INNER JOIN company ON gotstudentactivity.companyId = company.companyId ';
	$sql .= 'WHERE gotstudentactivity.from BETWEEN SUBDATE(CURRENT_DATE, INTERVAL 1 YEAR) AND CURRENT_DATE OR gotstudentactivity.to BETWEEN SUBDATE(CURRENT_DATE, INTERVAL 1 YEAR) AND CURRENT_DATE;';
	
	// Executing query
	$query = $db->prepare($sql);
	$query->execute();
	
	// Fetching data
	$result = $query->fetchAll(PDO::FETCH_ASSOC);
	
	// Rendering data
	$categories = $series = Array();
	$initiate = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

	for ($i = 11; $i >= 0; --$i)
		array_push($categories, date('M-y', mktime(0, 0, 0, date('m')-$i, date('d'), date('y'))));
	
	foreach ($result as $item) {
		$k = 0;
		$size = count($series);
		while ($k < $size) {
			if ($series[$k]['name'] == $item['COMPANY'])
				break;
			++$k;
		}
		if ($k == $size) {
			array_push($series, Array('name' => $item['COMPANY'], 'data' => $initiate));
			++$size;
		}
		
		for ($i = 0; $i < 12; ++$i) {
			$done = false;
			if ($item['FROM'] == $categories[$i]) {
				$done = true;
				for ($c = 0; $c < $size; ++$c) {
					if ($series[$c]['name'] == $item['COMPANY']) {
						for ($j = $i; $j < 12 && $categories[$j-1] != $item['TO']; ++$j) {
							$series[$c]['data'][$j]++;
						}
						break;
					}
				}
			}
			if (!$done && $item['TO'] == $categories[$i]) {
				for ($c = 0; $c < $size; ++$c) {
					if ($series[$c]['name'] == $item['COMPANY']) {
						for ($j = $i; $j >= 0 && $categories[$j] != $item['FROM']; --$j) {
							$series[$c]['data'][$j]++;
						}
						break;
					}
				}
			}
		}
	}
	echo json_encode($series);
	
} catch (Exception $e) {
	echo $e->getMessage();
}

?>